<?php
/**
 * Created by PhpStorm.
 * User: von
 * Date: 2017/12/5
 * Time: 16:18
 */
namespace backend\models;

use common\models\Country;
use common\models\Package;
use common\models\PackageCity;
use common\models\Responses;
use common\models\UserModel;
use yii\db\ActiveRecord;
use yii\db\Query;
use yii\helpers\ArrayHelper;

class SearchPackage extends ActiveRecord{

    /*
     * 通过制定条件搜索
     * @params:$data arr 查询条件
     *         $isCount int 是否获取总个数:0不获取1获取
     *         $pages  分页时用到的参数，数据总个数
     */
    public static function getPackageData($data,$isCount=0,$pages){
        if (!empty($data)&&!empty($data['keyWords'])){
            $keyWords = $data['keyWords'];
            $where_keywords = ['like','title',$keyWords];
        }else{
            $where_keywords = [];
        }
        if (!empty($data)&&!empty($data['principal'])){
            $principal = $data['principal'];
            $where_principal = ['and',['>=','principal',$principal['start']],['<=','principal',$principal['end']]];
        }else{
            $where_principal = [];
        }
        if (!empty($data)&&!empty($data['category'])){
            $category = $data['category'];
            $where_category = ['=','category',$category['val']];
        }else{
            $where_category = [];
        }
        if (!empty($data)&&!empty($data['packageState'])){
            $packageState = $data['packageState'];
            $where_packageState =  ['=','state',$packageState['val']];
        }else{
            $where_packageState = [];
        }
        if (!empty($data)&&!empty($data['packageType'])){
            $packageType = $data['packageType'];
            $where_packageType =  ['=','type',$packageType['val']];
        }else{
            $where_packageType = [];
        }
        if (!empty($data)&&!empty($data['owner'])){
            $owner = $data['owner'];
//            $where_owner =  ['=','owner',$owner];
            $ownerID = (new \yii\db\Query())->select('id')->from(UserModel::tableName())->where(['like','realname',$owner])->all();
            $ownerIDArr = [];
            if (!empty($ownerID)){
                foreach ($ownerID as $k => $v) {
                    $ownerIDArr[$k]=$v['id'];
                }
            }
            $where_owner =  ['in','p.owner',$ownerIDArr];
        }else{
            $where_owner = [];
        }
        //--------------资产包负责人------start
        if (!empty($data)&&!empty($data['charge'])){
            $charge = $data['charge'];
            $chargeId = [];   //不带逗号的负责人
            $chargeIdS = [];  //带逗号的负责人
            $chargeArr = (new \yii\db\Query())->from(User::tableName())->where(['like','realname',$charge])->all();
            if (!empty($chargeArr)){
                    foreach ($chargeArr as $k => $v) {
                        $chargeId[] = $v['id'];
                        $chargeIdS[] = ','.$v['id'].',';
                    }
            }
            if (!empty($chargeId)||!empty($chargeIdS)){
                $where_chargeArr = ['or',['in','pi.leader',$chargeId],['or like','pi.leader',$chargeIdS]];
            }else{
                $where_chargeArr = [];
            }
        }else{
            $where_chargeArr = [];
        }
        //--------------资产包负责人------end

        //---------------项目总监---------
        if (!empty($data)&&!empty($data['leader'])){
            $zongjian = $data['leader'];
            $departmentName = (new \yii\db\Query())->select('id,department')->from(UserModel::tableName())->where(['like','realname',$zongjian])->andWhere(['=','post','BranchManager'])->all();
            $leaderIDArr = [];
            if (!empty($departmentName)){
                foreach ($departmentName as $k => $v) {
                    $leaderIDArr[$k]=$v['id'];
                }
            }
            if (!empty($leaderIDArr)){
                $where_leaderArr = ['in','pi.zongjian',$leaderIDArr];
            }else{
                $where_leaderArr = [];
            }
//            $leaderIDArr = [];
//            if (!empty($departmentName)){
//                foreach ($departmentName as $k => $v) {
//                    $leaderIDArr[$k]=$v['department'];
//                }
//            }
//            $where_leaderDepartment =  ['in','department',$leaderIDArr];
//            //查找该部门下所有人
//            $allUser =  (new \yii\db\Query())->select('id')->from(UserModel::tableName())->where($where_leaderDepartment)->all();
//            $leaderArr = [];
//            if (!empty($allUser)){
//                foreach ($allUser as $k => $v) {
//                    $leaderArr[$k] = $v['id'];
//                }
//            }
//            if (!empty($leaderArr)){
//                $where_leaderArr = ['or',['pi.leader'=>$leaderArr]];
//            }else{
//                $where_leaderArr =  '1!=1';
//            }
        }else{
            $where_leaders = [];
            $where_leaderArr = [];
        }
        if (!empty($data)&&!empty($data['province'])){
            $province = $data['province'];
            $where_province = ['=','province',$province];
        }else{
            $where_province = [];
        }


        if (!empty($data)&&!empty($data['city'])){
            $city = $data['city'];
            //$where_city = ['=','city',$city];
            $where_city = "FIND_IN_SET( $city , p.city)";
        }else{
            $where_city = [];
        }

        //资产包允许多个城市
//        $where_city = [];
//        if (isset($data['city']) && $data['city'] !=0 ) {
//            $packageCityList = (new Query())
//                ->select(['id', 'package_id'])
//                ->from(PackageCity::tableName())
//                ->where(['city_id'=>$data['city']])
//                ->orderBy(['id' => SORT_ASC])
//                ->all();
//
//            if (!empty($packageCityList)) {
//                $ids = ArrayHelper::getColumn($packageCityList, 'package_id');
//                $where_city = ['in','p.id',$ids];
//            }
//        }

        //团队成员搜索
        if (!empty($data)&&!empty($data['teams'])){
            $teams = $data['teams'];
//            $where_teams = ['=','due_diligence',$teams];
            $teamsID = (new \yii\db\Query())->select('id')->from(UserModel::tableName())->where(['like','realname',$teams])->all();
            $teamsIDArr = [];
            $leaderIDArr = [];
            if (!empty($teamsID)){
                foreach ($teamsID as $k => $v) {
                    $teamsIDArr[$k]=','.$v['id'].',';
                    $leaderIDArr[$k]=$v['id'];
                }
            }
            $where_teams =  ['or',['or like','pi.due_diligence',$teamsIDArr],['or like','pi.pre_evaluation',$teamsIDArr],['in','pi.leader',$leaderIDArr],['or like','pi.leader',$teamsIDArr]];
        }else{
            $where_teams = [];
        }

        //资产包行业
        if (!empty($data)&&!empty($data['industry'])){
            $industry = $data['industry'];
            $where_industry = ['like','industry',$industry];
        }else{
            $where_industry = [];
        }
        //排序
        //--2018年3月2日11:01:51 fly  --No--START
        if (!empty($data['orderGroup'])){
            switch ($data['orderGroup']){
                case 1:
                    $where_order = 'p.title ASC';
                    break;
                case 2:
                    $where_order = 'p.title DESC';
                    break;
                case 3:
                    $where_order = 'p.type ASC';
                    break;
                case 4:
                    $where_order = 'p.type DESC';
                    break;
                case 5:
                    $where_order = 'p.city ASC';
                    break;
                case 6:
                    $where_order = 'p.city DESC';
                    break;
                case 7:
                    $where_order = 'p.principal ASC';
                    break;
                case 8:
                    $where_order = 'p.principal DESC';
                    break;
                case 9:
                    $where_order = 'p.interest ASC';
                    break;
                case 10:
                    $where_order = 'p.interest DESC';
                    break;
                case 11:
                    $where_order = 'p.hushu ASC';
                    break;
                case 12:
                    $where_order = 'p.hushu DESC';
                    break;
                case 13:
                    $where_order = 'pi.leader ASC';
                    break;
                case 14:
                    $where_order = 'pi.leader DESC';
                    break;
                case 15:
                    $where_order = 'p.created_at ASC';
                    break;
                case 16:
                    $where_order = 'p.created_at DESC';
                    break;
                case 17:
                    $where_order = 'p.state ASC';
                    break;
                case 18:
                    $where_order = 'p.state DESC';
                    break;
                default:
                    $where_order = 'p.created_at DESC';
                    break;
            }
        }else{
            $where_order = 'p.created_at DESC';
        }
//        if (!empty($data)&&!empty($data['order'])){
//            $where_order = 'p.created_at ASC';
//        }else{
//            $where_order = 'p.created_at DESC';
//        }
        //--2018年3月2日11:01:51 fly  --No--END


        //是否上传完成1完成 0未完成
        $is_finishedVal = \Yii::$app->params['is_finished'][1]['val'];
        $is_finished = ['=','p.is_finished',$is_finishedVal];
        if ($isCount!=0){
            $countData = (new \yii\db\Query())->select('count(*) as count')->from('package as p')->leftJoin('package_initiation as pi','p.id = pi.package_id')->leftJoin('package_transfer as pt','p.id = pt.package_id')->where($where_keywords)->andWhere($where_principal)->andWhere($where_category)->andWhere($where_packageState)->andWhere($where_packageType)->andWhere($where_owner)->andWhere($where_province)->andWhere($where_city)->andWhere($where_teams)->andWhere($where_leaderArr)->andWhere($is_finished)->andWhere($where_industry)->andWhere($where_chargeArr)->groupBy('p.id')->count('*');
            return  $countData;
        }else{
            $data = (new \yii\db\Query())->select('p.state,p.created_at,p.province,p.city,pi.leader,pi.due_diligence,p.interest,p.hushu,p.principal,p.category,p.type,p.title,p.id,pi.package_id,p.created_at')->from('package as p')->leftJoin('package_initiation as pi','p.id = pi.package_id')->leftJoin('package_transfer as pt','p.id = pt.package_id')->where($where_keywords)->andWhere($where_principal)->andWhere($where_category)->andWhere($where_packageState)->andWhere($where_packageType)->andWhere($where_owner)->andWhere($where_province)->andWhere($where_city)->andWhere($where_teams)->andWhere($where_leaderArr)->andWhere($is_finished)->andWhere($where_industry)->andWhere($where_chargeArr)->offset($pages->offset)->limit($data['limit'])->addOrderBy($where_order)->groupBy('p.id')->all();
        }
        //获取城市名称
        $countryList = \common\models\Country::getTitleList();
        foreach ($data as $k=>$v) {
            $data[$k]['provincename'] = (new \yii\db\Query())->from(Country::tableName())->where(['=','id',$v['province']])->one();
            $data[$k]['cityname']     = (new \yii\db\Query())->from(Country::tableName())->where(['=','id',$v['city']])->one();

            //资产包城市多选
            $cityNameList = '';
            if ($v['city']!= '') {
                $arr = explode(',',$v['city']);
                $array = array();
                if (!empty($arr)) {
                    foreach ($arr as $value) {
                        $array[] = $countryList[(int)$value];
                    }
                }
                $cityNameList = join('、', $array);
            }
            $data[$k]['cityNameList'] = $cityNameList;
        }
        //--------------------获取尽调成员名称--------------------
        foreach ($data as $k => $v) {
            $packageUserId = array_filter(explode(',',$v['due_diligence']));
            if (!empty($packageUserId)){
                $data[$k]['due_diligencename'] = (new \yii\db\Query())->select('id,realname,department')->from('user as u')->leftJoin('auth_assignment as aa','u.id = aa.user_id')->leftJoin('auth_item as ai','ai.name = aa.item_name')->where(['in','id',$packageUserId])->all();
            }else{
                $data[$k]['due_diligencename'] = [];
            }
        }
        //--------------------查询负责人名称--------------------
        if (!empty($data)){
            foreach ($data as $k => $v) {
                $packageLeaderId = array_filter(explode(',',$v['leader']));
                if (!empty($packageLeaderId)){
                    $data[$k]['LeaderName'] = (new \yii\db\Query())->select('id,realname,ai.description as bumen,department')->from('user as u')->leftJoin('auth_assignment as aa','u.id = aa.user_id')->leftJoin('auth_item as ai','ai.name = u.department')->where(['in','id',$packageLeaderId])->one();
                }else{
                    $data[$k]['LeaderName'] = [];
                }
            }
        }
        //-----------------负责人所在部门的领导----------------------------------------
        if (!empty($data)){
            foreach ($data as $k => $v) {
                if (!empty($v['LeaderName']['department'])){
                    $data[$k]['LeaderManagerName'] = (new \yii\db\Query())->select('id,realname,department')->from('user as u')->where(['=','department',$v['LeaderName']['department']])->andWhere(['=','department','BranchManager'])->one();
                    }else{
                        $data[$k]['LeaderManagerName'] = [];
                    }
            }
        }
        return json_encode(Responses::response200($data));
    }
    /*
     * 获取省份
     */
    public static function getProvinces($where=true){
        $provinceData = (new \yii\db\Query())->from(Country::tableName())->where($where)->all();
        return $provinceData;
    }
    /*
     *获取城市
     */
    public static function getCitys($provinceID=1){
        $provinceData = (new \yii\db\Query())->from(Country::tableName())->where(['=','pid',$provinceID])->all();
        return $provinceData;
    }
    public static function getCity($provinceID=1){
        $provinceData = (new \yii\db\Query())->from(Country::tableName())->where(['=','id',$provinceID])->all();
        return $provinceData;
    }
    /*
     * 获取资产包标题
     */
    public static function getKeywodsTitle($keywords){
        $provinceData = (new \yii\db\Query())->from(Package::tableName())->where(['like','title',$keywords])->all();
        $provinceDataCount = (new \yii\db\Query())->select('id')->from(Package::tableName())->where(['like','title',$keywords])->count('*');
        if (!empty($provinceData)){
            return json_encode(Responses::response200($provinceData,$provinceDataCount));
        }
        return json_encode(Responses::response400('无数据'));
    }

    /**
     * 通过资产包id获取资产包信息
     * @param $package_id
     */
    public static function getPackageOfPackId($package_id){
            $packageData = (new \yii\db\Query())->from(Package::tableName())->where(['=','id',$package_id])->one();
            return $packageData;
    }
}